from __future__ import annotations

import os
import threading
from typing import Optional, Iterable, Any

from loguru import logger
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, scoped_session, DeclarativeBase
from sqlalchemy.engine import Engine


class Base(DeclarativeBase):
    pass


class DatabaseManager:
    _instance: Optional["DatabaseManager"] = None
    _lock = threading.Lock()

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = super(DatabaseManager, cls).__new__(cls)
        return cls._instance

    def __init__(self, db_name: str = "trader_settings.db"):
        if getattr(self, "_initialized", False):
            return

        self.db_name = db_name
        db_url = self._to_sqlite_url(db_name)

        # SQLite URL, allow cross-thread usage
        self.engine: Engine = create_engine(
            db_url,
            connect_args={"check_same_thread": False},
            echo=False,
        )
        self.SessionLocal = scoped_session(sessionmaker(bind=self.engine, autoflush=False, autocommit=False))
        self._initialized = True
        logger.debug(f"SQLAlchemy engine created for {db_url}")

    @staticmethod
    def _to_sqlite_url(db_path: str) -> str:
        # Use absolute path to avoid multiple DBs from different working dirs
        abs_path = os.path.abspath(db_path)
        return f"sqlite:///{abs_path}"

    # Session management
    def get_session(self):
        return self.SessionLocal()

    def close_session(self):
        try:
            self.SessionLocal.remove()
        except Exception:
            pass

    # Backward-compat for old DAO methods that executed raw SQL
    def get_connection(self):
        return self.engine.raw_connection()

    def execute_query(self, query: str, params: Iterable[Any] | None = None):
        try:
            with self.engine.connect() as conn:
                result = conn.execute(text(query), params or {})
                rows = result.fetchall()
                return rows
        except Exception as e:
            logger.error(f"execute_query failed: {e}")
            return None

    def execute_update(self, query: str, params: Iterable[Any] | None = None):
        try:
            with self.engine.begin() as conn:
                result = conn.execute(text(query), params or {})
                try:
                    # SQLite supports lastrowid via raw DB-API; for SQLAlchemy we don't guarantee it
                    return result.lastrowid if hasattr(result, "lastrowid") else None
                except Exception:
                    return None
        except Exception as e:
            logger.error(f"execute_update failed: {e}")
            return None

    def create_all(self):
        # Import models here to avoid circular imports
        try:
            from dao import models  # noqa: F401
        except Exception as e:
            logger.debug(f"Import dao.models failed during create_all (may be first run): {e}")
        Base.metadata.create_all(self.engine)


# Global instance and ensure tables exist
db_manager = DatabaseManager()
try:
    db_manager.create_all()
except Exception as e:
    logger.warning(f"Failed to create tables: {e}")

